package com.pcbly.web.persistence.form;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Statement;
import java.sql.Types;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Set;

import org.hibernate.Session;
import org.hibernate.engine.SessionImplementor;

import com.pcbly.web.persistence.HibernateBaseDao;

@SuppressWarnings("unchecked")
public class MacroComponentHibernateDao extends HibernateBaseDao implements
		MacroComponentDao {

	/**
	 * 
	 */
	private static final long serialVersionUID = -4519561517418247126L;

	public MacroComponentHibernateDao() {
		super();
		// TODO Auto-generated constructor stub
	}

	public List<HashMap<String, String>> findOptionBySql(String sql)
			throws Exception {
		// logger.debug("query list1::::::::::::::::::" + sql);
		// sql=sql+" and 1=?";
		Session session = getSession();
		Connection conn = ((SessionImplementor) session).getJDBCContext()
				.borrowConnection();
		Statement ps = conn.createStatement();
		// logger.debug("query list2::::::::::::::::::" + sql);

		ResultSet rs = ps.executeQuery(sql);
		List<HashMap<String, String>> list = new ArrayList<HashMap<String, String>>();
		while (rs.next()) {
			HashMap<String, String> map = new HashMap<String, String>();
			ResultSetMetaData rsmd = rs.getMetaData();
			// logger.debug("query list3::::::::::::::::::" +
			// rsmd.getColumnCount());
			if (rsmd.getColumnCount() > 1) {
				// logger.debug("query list3::::::::::::::::::" +
				// rsmd.getColumnName(1));
				// logger.debug("query list3::::::::::::::::::" +
				// rsmd.getColumnName(2));
				map.put("value", rs.getString(rsmd.getColumnName(1)));
				map.put("text", rs.getString(rsmd.getColumnName(2)));
			} else {
				map.put("value", rs.getString(rsmd.getColumnName(1)));
				map.put("text", rs.getString(rsmd.getColumnName(1)));
			}

			list.add(map);
		}
		if (rs != null) {
			rs.close();
			rs = null;
		}
		if (ps != null) {
			ps.close();
			ps = null;
		}
		if (conn != null) {
			conn.close();
		}
		// logger.debug("query list4::::::::::::::::::" + sql);
		return list;
	}

	@Override
	public void saveDataHashMap(HashMap<String, String> map) throws Exception {
		// TODO Auto-generated method stub
		Set<String> set = map.keySet();
		Iterator<String> it = set.iterator();
		Session session = getSession();
		Connection conn = ((SessionImplementor) session).getJDBCContext()
				.borrowConnection();
		conn.setAutoCommit(false);
		Statement state = conn.createStatement();
		while (it.hasNext()) {
			String sql = map.get(it.next());
			if(logger.isDebugEnabled()){
				System.out.println("sql:::::::::::::" + sql);
				logger.debug("sql:::::::::::::" + sql);
			}
			state.execute(sql);
		}
		conn.commit();
		if (state != null) {
			state.close();
			state = null;
		}
		if (conn != null) {
			conn.close();
		}
	}

	@Override
	public HashMap<String, String> findDataByTabelNameAndId(String tableName,
			String id) throws Exception {
		// TODO Auto-generated method stub
		try {
			Session session = getSession();
			Connection conn = ((SessionImplementor) session).getJDBCContext()
					.borrowConnection();
			Statement ps = conn.createStatement();
			StringBuilder sql = new StringBuilder();
			sql.append("select * from ");
			sql.append(tableName);
			sql.append(" where  id='");
			sql.append(id);
			sql.append("' ");
			
			if (logger.isDebugEnabled()){
				System.out.println(sql);
				logger.debug(sql);
			}
			ResultSet rs = ps.executeQuery(sql.toString());
			List<HashMap<String, String>> list = new ArrayList<HashMap<String, String>>();
			while (rs.next()) {
				HashMap<String, String> map = new HashMap<String, String>();
				ResultSetMetaData rsmd = rs.getMetaData();
				int icount = rsmd.getColumnCount();
				for (int i = 1; i <= icount; i++) {
					int t = rsmd.getColumnType(i);
//					System.out.println(rsmd.getColumnName(i));
//					System.out.println(rs.getString(rsmd.getColumnName(i)));
					if (Types.DATE == t || t == Types.TIMESTAMP) {
						SimpleDateFormat dateFormat = new SimpleDateFormat(
								"yyyy-MM-dd");						
						if(!(rs.getDate(rsmd.getColumnName(i))==null)){
							map.put(rsmd.getColumnName(i), dateFormat.format(rs
									.getDate(rsmd.getColumnName(i))));
						}else{
							map.put(rsmd.getColumnName(i), null);
						}
						
					} else if (t == Types.TIME) {
						
						SimpleDateFormat dateFormat = new SimpleDateFormat(
								"HH:mm");
//						System.out.println(rsmd.getColumnName(i));
//						System.out.println(rs.getTime(rsmd.getColumnName(i)));
						if(!(rs.getTime(rsmd.getColumnName(i))==null)){
							map.put(rsmd.getColumnName(i), dateFormat.format(rs
									.getTime(rsmd.getColumnName(i))));
						}else{
							map.put(rsmd.getColumnName(i), null);
						}
					} else {
//						System.out.println(rsmd.getColumnName(i));
//						System.out.println(rs.getString(rsmd.getColumnName(i)));
						map.put(rsmd.getColumnName(i), rs.getString(rsmd
								.getColumnName(i)));
					}
				}
				list.add(map);
			}
			if (rs != null) {
				rs.close();
				rs = null;
			}
			if (ps != null) {
				ps.close();
				ps = null;
			}
			if (conn != null) {
				conn.close();
			}
			HashMap<String, String> ma = new HashMap<String, String>();
			if (!(list.isEmpty()||list.size()<1)) {
//				System.out.println(sql);
//				logger.debug(sql);
				ma = list.get(0);
//				System.out.println(ma);
//				logger.debug(ma);
			}
			return ma;
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}
	}
}
